﻿'Description:收料确认(erp过来的集件清单确认)
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-05-20
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient

Public Class CollectERPConfirmBusines
    Private m_ContainerObj As New uLMSContainerBusiness
    ''' <summary>
    ''' 获取部装工艺下物料信息表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMaterialDataList(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerIDList"))
        Dim sb As New StringBuilder
        If strFirstSecondIssue = "2" Then
            sb.AppendLine("SELECT DISTINCT cm.COLLECTERPMATERIALID,cm.productname,cm.productbatchno,pm.checkassembleqty firstassembleqty,im.issueqty ,im.containerid,ep.CONTAINERNAME, ")
        Else
            sb.AppendLine("SELECT DISTINCT cm.COLLECTERPMATERIALID,cm.productname,cm.productbatchno,pm.firstassembleqty,im.issueqty ,im.containerid,ep.CONTAINERNAME, ")
        End If
        Dim sbb As New StringBuilder
        sbb.AppendLine(<string>
                    p.description productdesc,pm.PARTTYPE,nvl(imc.confirmqty,0) totalCollectQty,(im.issueqty-nvl(oneCONFIRMQTY,0)) ChangeQTY,im.issueqty-nvl(oneCONFIRMQTY,0) NOTOTALCOLLECTQTY
                     ,nvl(oneCONFIRMQTY,0) oneCONFIRMQTY 
                     FROM issueerpmaterial im 
                     LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid 
                     LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid 
                     LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid 
                     LEFT JOIN product p ON pm.productid=p.productid --多次生成任务单--ERP批次集件 数量
                     LEFT JOIN (SELECT SUM(CONFIRMQTY) oneCONFIRMQTY,COLLECTERPMATERIALID FROM COLLECTERPMATERIALCONFIRM where CONTAINERID='{0}' GROUP BY COLLECTERPMATERIALID,CONTAINERID) oneCon ON oneCon.COLLECTERPMATERIALID=im.collecterpmaterialid 
                     LEFT JOIN ( --任务单--零件集件数量--
                       SELECT iss.PARTMENUINFOID,iss.ContainerID,SUM(confirmqty) confirmqty FROM issueerpmaterial iss
                       LEFT JOIN (select SUM(confirmqty) confirmqty,COLLECTERPMATERIALID from COLLECTERPMATERIALCONFIRM  where CONTAINERID='{0}' group by COLLECTERPMATERIALID,ContainerID) con ON con.COLLECTERPMATERIALID=iss.COLLECTERPMATERIALID 
                       GROUP BY iss.PARTMENUINFOID,iss.ContainerID
                     ) imc ON  imc.ContainerID=im.ContainerID AND imc.PARTMENUINFOID=im.PARTMENUINFOID              
                     where im.issueqty>0             
                      </string>)
        sb.AppendFormat(sbb.ToString, para("ContainerIDList").Replace(",", "','"))
        If para.ContainsKey("ContainerNoList") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNoList")) Then
                sb.AppendFormat(" and im.ContainerNo in ('{0}') ", para("ContainerNoList").Replace(",", "','"))
            End If
        End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and im.workflowstepid ='{0}' ", para("workflowstepid"))
            End If
        End If
        If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件
            sb.AppendLine(" AND nvl(pm.checkassembleqty,0)>0")
        End If
        sb.AppendLine(<string>
                            Order by cm.productname,cm.productbatchno asc 
                      </string>)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' add by longb 2022-05-21
    ''' 根据 workflowid 查询部件所需零件的【单台数量】【已确认数量】
    ''' </summary>
    ''' <param name="workflowid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetFirstAssembleQtyconfirmqty(ByVal workflowid As String, ByVal CONTAINERNO As String) As DataTable
        Dim strsql As String =
            <string>
                SELECT nvl(SUM(FirstAssembleQty),0) FirstAssembleQty,nvl(SUM(confirmqty),0)confirmqty FROM (
                 SELECT bb.bomname,pl.Bigsmallpart,pl.Productid,p.description,pb.productname,b.workflowid 
                    ,sum(pl.Qtyrequired) FirstAssembleQty,cm.confirmqty,cm.COLLECTERPMATERIALID 
                     FROM bom b 
                    LEFT JOIN productmateriallistitem PL ON b.bomid=pl.Bomid 
                      LEFT JOIN billtype bt ON b.billtypeid=bt.billtypeid 
                    LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid 
                    LEFT JOIN product p ON pl.Productid=p.productid 
                    LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid 
                    LEFT JOIN(
                     SELECT cm.PRODUCTNAME,ep.WORKFLOWID,nvl(SUM(cm1.confirmqty),0) confirmqty,cm1.COLLECTERPMATERIALID,ep.CONTAINERNO FROM issueerpmaterial im 
                     LEFT JOIN enginepartplan ep ON ep.containerid=im.containerid 
                     LEFT JOIN Collecterpmaterial cm ON cm.collecterpmaterialid= im.collecterpmaterialid
                     LEFT JOIN COLLECTERPMATERIALCONFIRM cm1 ON cm1.COLLECTERPMATERIALID=cm.COLLECTERPMATERIALID
                     --WHERE ep.WORKFLOWID='480026800000381c' 
                     GROUP BY cm.PRODUCTNAME,ep.WORKFLOWID,cm1.COLLECTERPMATERIALID,ep.CONTAINERNO
                    ) cm ON cm.WORKFLOWID=b.WORKFLOWID AND cm.PRODUCTNAME=pb.productname
                    WHERE 1=1 AND bt.billtypename='大小件目录' 
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        sb.AppendFormat(" and b.workflowid= '{0}'", workflowid)
        sb.AppendFormat(" and cm.CONTAINERNO= '{0}'", CONTAINERNO)
        sb.AppendFormat(" group by bb.bomname,pl.Bigsmallpart, b.bomid,pl.Productid,pb.productname,p.description,b.workflowid,cm.confirmqty,cm.COLLECTERPMATERIALID)")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        GetFirstAssembleQtyconfirmqty = dt
    End Function

    Function GetFirstAssembleQtyAndConfirmqty(ByVal workflowid As String, ByVal CONTAINERNO As String, ByVal CONTAINERID As String) As DataTable
        Dim strsql As String =
            <string>
                 SELECT bb.bomname,pl.Bigsmallpart,pl.Productid,p.description,pb.productname,b.workflowid 
                    ,pl.Qtyrequired-nvl(sum(cm.confirmqty),0) confirmqty--,cm.COLLECTERPMATERIALID 
                     FROM bom b 
                    LEFT JOIN productmateriallistitem PL ON b.bomid=pl.Bomid 
                    LEFT JOIN billtype bt ON b.billtypeid=bt.billtypeid 
                    LEFT JOIN bombase bb ON b.bombaseid=bb.bombaseid 
                    LEFT JOIN product p ON pl.Productid=p.productid 
                    LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid 
                    LEFT JOIN(
                     SELECT cm.PRODUCTNAME,ep.WORKFLOWID,cm1.confirmqty confirmqty,cm1.COLLECTERPMATERIALID,ep.CONTAINERNO,EP.CONTAINERID FROM issueerpmaterial im 
                     LEFT JOIN enginepartplan ep ON ep.containerid=im.containerid 
                     LEFT JOIN Collecterpmaterial cm ON cm.collecterpmaterialid= im.collecterpmaterialid
                     LEFT JOIN (SELECT nvl(SUM(confirmqty),0) confirmqty,COLLECTERPMATERIALID FROM COLLECTERPMATERIALCONFIRM GROUP BY COLLECTERPMATERIALID) cm1 ON cm1.COLLECTERPMATERIALID=cm.COLLECTERPMATERIALID
                     --WHERE ep.WORKFLOWID='480026800000381c' 
                     GROUP BY cm.PRODUCTNAME,ep.WORKFLOWID,cm1.COLLECTERPMATERIALID,ep.CONTAINERNO,ep.CONTAINERID,cm1.confirmqty
                    ) cm ON cm.WORKFLOWID=b.WORKFLOWID AND cm.PRODUCTNAME=pb.productname
                    WHERE 1=1 AND bt.billtypename='大小件目录' 
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT COUNT(1) TOTAL,COUNT(DECODE((CASE WHEN CONFIRMQTY<=0 THEN 0 ELSE 1 END),0,1,NULL)) CCOUNT   FROM (")
        sb.AppendLine(strsql)
        sb.AppendFormat(" and b.workflowid= '{0}'", workflowid)
        sb.AppendFormat(" and cm.CONTAINERNO= '{0}'", CONTAINERNO)
        sb.AppendFormat(" and cm.CONTAINERID= '{0}'", CONTAINERID)
        sb.AppendFormat(" group by bb.bomname,pl.Bigsmallpart, b.bomid,pl.Productid,pb.productname,p.description,b.workflowid,pl.Qtyrequired)--cm.confirmqty,cm.COLLECTERPMATERIALID)")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 获取辅材库存送检信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAuxMaterialStore(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT * FROM (SELECT MATNR,MAKTX,MTART,SPECIFICATION,CHARG,ISSENDCHECK,to_char(VLDAT,'yyyy-MM-dd')VLDAT,SENDCHECKCYCLE")
        sb.AppendLine(",CASE WHEN VLDAT IS NULL THEN '无需送检'ELSE (CASE WHEN ISSENDCHECK='是' AND ROUND(TO_NUMBER(SYSDATE-VLDAT))>0 THEN '逾期未检' WHEN ISSENDCHECK='是' AND ROUND(TO_NUMBER(VLDAT-SYSDATE))<=SENDCHECKCYCLE THEN '按期送检' ELSE '无需送检' END ) END SendSTATUS")
        sb.AppendLine(<string>
                        FROM ( SELECT MATNR,MAKTX,MTART,a.SPECIFICATION
                        ,Charg,decode(ISSENDCHECK,1,'是','否') ISSENDCHECK,VLDAT,nvl(SENDCHECKCYCLE,0) SENDCHECKCYCLE
	                    FROM (SELECT DISTINCT WERKS,MATNR,MAKTX,MTART,CHARG,ISSENDCHECK,SENDCHECKCYCLE,VLDAT  FROM T_LBJKC)t 
                        LEFT JOIN AUXMATERIAL a ON a.AUXMATERIALNAME =t.MATNR
                        LEFT JOIN (SELECT ax.AUXMATERIALNAME,ax.SAFESTOCKQTY,ax.MINSTOCKQTY,u.ATTRIBUTEVALUE FROM AuxMaterialStock ax 
			            LEFT JOIN USERATTRIBUTE u ON u.PARENTID =ax.FACTORYID AND u.USERATTRIBUTENAME ='分厂代码') ams ON ams.AUXMATERIALNAME=t.MATNR and ams.ATTRIBUTEVALUE=t.WERKS 
                        WHERE t.MATNR Is Not NULL 
                      </string>.Value)
        If para.ContainsKey("Name") Then
            If Not String.IsNullOrWhiteSpace(para("Name")) Then
                sb.AppendFormat(" AND MATNR like '%{0}%'", para("Name"))
            End If
        End If
        If para.ContainsKey("Description") Then
            If Not String.IsNullOrWhiteSpace(para("Description")) Then
                sb.AppendFormat(" AND MAKTX like '%{0}%'", para("Description"))
            End If
        End If
        If para.ContainsKey("Charg") Then
            If Not String.IsNullOrWhiteSpace(para("Charg")) Then
                sb.AppendFormat(" AND Charg like '%{0}%'", para("Charg"))
            End If
        End If
        sb.AppendLine("))")
        If para.ContainsKey("SendStatus") Then
            If Not String.IsNullOrWhiteSpace(para("SendStatus")) Then
                sb.AppendFormat(" WHERE SendStatus IN('{0}')", para("SendStatus").Replace(",", "','"))
            End If
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 获取辅材安全库存信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAuxMaterialSeafStore(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT * FROM (SELECT MATNR,MAKTX,MTART,LABST,EINME,UMLMC,SPECIFICATION,MINSTOCKQTY,SAFESTOCKQTY,")
        sb.AppendLine("CASE WHEN SUMQTY>MINSTOCKQTY AND SUMQTY<SAFESTOCKQTY THEN '建议采购' WHEN SUMQTY<=MINSTOCKQTY THEN '急需采购' ELSE '库存正常' END STATUS")
        sb.AppendLine(<string>
                         FROM ( 
                        SELECT MATNR,MAKTX,MTART,nvl(LABST,0) LABST,nvl(EINME,0) EINME,nvl(UMLMC,0) UMLMC,a.SPECIFICATION,nvl(ams.MINSTOCKQTY,0) MINSTOCKQTY,nvl(ams.SAFESTOCKQTY,0)  SAFESTOCKQTY, NVL(LABST,0)+NVL(EINME,0)+NVL(UMLMC,0) SUMQTY
	                    FROM (SELECT WERKS,MATNR,MAKTX,MTART,SUM(nvl(LABST,0))LABST,sum(nvl(EINME,0))EINME,SUM(nvl(UMLMC,0))UMLMC FROM T_LBJKC 
							  GROUP BY WERKS,MATNR,MAKTX,MTART) t 
	                    LEFT JOIN AUXMATERIAL a ON a.AUXMATERIALNAME =t.MATNR
                        LEFT JOIN (SELECT ax.AUXMATERIALNAME,ax.SAFESTOCKQTY,ax.MINSTOCKQTY,u.ATTRIBUTEVALUE FROM AuxMaterialStock ax 
							LEFT JOIN USERATTRIBUTE u ON u.PARENTID =ax.FACTORYID AND u.USERATTRIBUTENAME ='分厂代码') ams ON ams.AUXMATERIALNAME=t.MATNR and ams.ATTRIBUTEVALUE=t.WERKS 
	                    WHERE t.MATNR IS NOT NULL  
                      </string>.Value)
        If para.ContainsKey("Name") Then
            If Not String.IsNullOrWhiteSpace(para("Name")) Then
                sb.AppendFormat(" AND MATNR like '%{0}%'", para("Name"))
            End If
        End If
        If para.ContainsKey("Description") Then
            If Not String.IsNullOrWhiteSpace(para("Description")) Then
                sb.AppendFormat(" AND MAKTX like '%{0}%'", para("Description"))
            End If
        End If
        sb.AppendLine("))")
        If para.ContainsKey("Status") Then
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" WHERE STATUS IN('{0}')", para("Status").Replace(",", "','"))
            End If
        End If
        
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

End Class
